package com.hao.oracle;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OracleGenerateDataDictionary {
    public static JdbcTemplate jdbcTemplate;

    public static void main(String[] args) {
        //启动IoC容器
        ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        //获取IoC容器中JdbcTemplate实例
        jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");

        //1.创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        GenerateTableDictionary(wb);
        //2.1如果需要多个，则继续创建即可
        HSSFSheet sheet1 = wb.createSheet("数据字典");
        GenerateDataDictionary(sheet1, "bj_dictionary_teams", "bj_dictionary", "col_name", "col_comment", "col_value","col_text","dic_id","dic_id");

        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream("d:/数据库说明文档.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("写出成功！");
    }

    //生成表说明
    public static void GenerateTableDictionary(HSSFWorkbook wb) {
        //2.在建立的工作簿中添加一个sheet，对应Excell文件中的工作簿，并设置工作簿名称
        String tableName = "";
        String comment = "";
        String columnName = "";
        String value = "";
        HSSFSheet sheet = null;

        List<Map<String, Object>> tables = queryAllTableNameAndComment();
        for (Map<String, Object> table : tables) {
            for (String key : table.keySet()) {
                if (key.equalsIgnoreCase("TABLE_NAME")) {
                    tableName = (String) table.get(key);
                }
                if (key.equalsIgnoreCase("COMMENTS")) {
                    comment = (String) table.get(key);
                }

                if (wb.getSheet(tableName) == null) {
                    sheet = wb.createSheet(tableName);
                } else {
                    sheet = wb.getSheet(tableName);
                }

                System.out.println("##########################################################");
                System.out.println("表名:" + tableName + "--说明:" + comment);
                if (StringUtils.isNotBlank(tableName)) {
                    List<Map<String, Object>> columns = queryColumnAndComment(tableName);
                    Map<String, String> pks = queryTablePk(tableName);
                    HSSFRow title = sheet.createRow(0);
                    title.createCell(0).setCellValue("表名");
                    title.createCell(1).setCellValue(comment);
                    title.createCell(2).setCellValue("物理表名");
                    title.createCell(3).setCellValue(tableName);
                    HSSFRow title1 = sheet.createRow(1);
                    title1.createCell(0).setCellValue("名称");
                    title1.createCell(1).setCellValue("类型");
                    title1.createCell(2).setCellValue("可为空");
                    title1.createCell(3).setCellValue("默认");
                    title1.createCell(4).setCellValue("主键");
                    title1.createCell(5).setCellValue("注释");
                    for (int i = 0; i < columns.size(); i++) {
                        Map<String, Object> column = columns.get(i);
                        HSSFRow row = sheet.createRow(i + 2);
                        for (String field : column.keySet()) {
                            //设置名称
                            if (field.equalsIgnoreCase("column_name")) {
                                columnName = (String) column.get(field);
                                String pk = pks.get(columnName);
                                row.createCell(0).setCellValue(columnName);
                                //判断是否是主键
                                if (StringUtils.isNotBlank(pk)) {
                                    //如果是主键，标注*
                                    row.createCell(4).setCellValue("*");
                                }
                            }

                            //设置类型
                            if (field.equalsIgnoreCase("DATA_TYPE")) {
                                value = (String) column.get(field);
                                if (value.equalsIgnoreCase("DATE") || value.equalsIgnoreCase("CLOB")) {
                                    row.createCell(1).setCellValue(value);
                                } else if (value.equalsIgnoreCase("NUMBER")) {
                                    String length = "";
                                    if (column.get("DATA_PRECISION") != null) {
                                        length = "(" + column.get("DATA_PRECISION") + ")";
                                    }
                                    row.createCell(1).setCellValue(value + length);
                                } else {
                                    String length = "(" + column.get("DATA_LENGTH") + ")";
                                    row.createCell(1).setCellValue(value + length);
                                }
                            }

                            //设置可为空
                            if (field.equalsIgnoreCase("NULLABLE")) {
                                value = (String) column.get(field);
                                row.createCell(2).setCellValue(value);
                            }

                            //设置默认
                            if (field.equalsIgnoreCase("DATA_DEFAUL")) {
                                value = (String) column.get(field);
                                row.createCell(3).setCellValue(value);
                            }

                            //设置注释
                            if (field.equalsIgnoreCase("comments")) {
                                comment = (String) column.get(field);
                                row.createCell(5).setCellValue(comment);
                            }
                        }
                        System.out.println("字段名:" + columnName + "--说明:" + comment);
                    }
                }
                System.out.println("##########################################################");
            }
        }
    }

    //生成数据字典说明文件
    public static void GenerateDataDictionary(HSSFSheet sheet, String tableName, String parentCodeColnumName, String parentValueColnumName, String codeColnumName, String valueColnumName) {
        String parenCode = "";
        HSSFRow title = sheet.createRow(0);
        title.createCell(0).setCellValue("字典code");
        title.createCell(1).setCellValue("字典名称");
        title.createCell(2).setCellValue("类型编码");
        title.createCell(3).setCellValue("类型名称");
        List<Map<String, Object>> mapList = queryDataDictionary(tableName, parentCodeColnumName);
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = mapList.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            if (parenCode.equals(map.get(parentCodeColnumName.toUpperCase()).toString())) {
                row.createCell(0).setCellValue("");
                row.createCell(1).setCellValue("");
            } else {
                row.createCell(0).setCellValue(map.get(parentCodeColnumName.toUpperCase()).toString());
                row.createCell(1).setCellValue(map.get(parentValueColnumName.toUpperCase()).toString());
            }
            row.createCell(2).setCellValue(map.get(codeColnumName.toUpperCase()).toString());
            row.createCell(3).setCellValue(map.get(valueColnumName.toUpperCase()).toString());
            parenCode = map.get(parentCodeColnumName.toUpperCase()).toString();
        }
        MergeSpaceRow(sheet);
    }

    //生成数据字典说明文件
    public static void GenerateDataDictionary(HSSFSheet sheet, String tableName, String parentTable, String parentCodeColnumName, String parentValueColnumName, String codeColnumName, String valueColnumName, String parentCommonId, String commonId) {
        String parenCode = "";
        HSSFRow title = sheet.createRow(0);
        title.createCell(0).setCellValue("字典code");
        title.createCell(1).setCellValue("字典名称");
        title.createCell(2).setCellValue("类型编码");
        title.createCell(3).setCellValue("类型名称");

        List<Map<String, Object>> mapList = queryDataDictionary(tableName,parentTable, parentCodeColnumName,parentValueColnumName,codeColnumName,valueColnumName,parentCommonId,commonId);
        for (int i = 0; i < mapList.size(); i++) {
            Map<String, Object> map = mapList.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            if (parenCode.equals(map.get(parentCodeColnumName.toUpperCase()).toString())) {
                row.createCell(0).setCellValue("");
                row.createCell(1).setCellValue("");
            } else {
                row.createCell(0).setCellValue(map.get(parentCodeColnumName.toUpperCase()).toString());
                row.createCell(1).setCellValue(map.get(parentValueColnumName.toUpperCase()).toString());
            }
            row.createCell(2).setCellValue(map.get(codeColnumName.toUpperCase()).toString());
            row.createCell(3).setCellValue(map.get(valueColnumName.toUpperCase()).toString());
            parenCode = map.get(parentCodeColnumName.toUpperCase()).toString();
        }
        MergeSpaceRow(sheet);
    }

    //查询字段名及字段说明
    public static List<Map<String, Object>> queryColumnAndComment(String tableName) {
        String sql = "select t.*,a.DATA_TYPE,a.DATA_LENGTH,a.DATA_PRECISION,a.DATA_SCALE,a.NULLABLE,a.DATA_DEFAULT " +
                "from user_col_comments t,user_tab_columns a " +
                "where t.table_name=a.TABLE_NAME and t.column_name=a.COLUMN_NAME and t.table_name ='" + tableName.toUpperCase() + "'";
        return jdbcTemplate.queryForList(sql);
    }

    //查询所有表名
    public static void queryAllTableName() {
        String sql = "select table_name from user_tables";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        mapList.forEach(map -> map.forEach((k, v) -> System.out.print(k + "--" + v)));
    }

    //查询表名及表说明
    public static List<Map<String, Object>> queryAllTableNameAndComment() {
        String sql = "select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name";
        return jdbcTemplate.queryForList(sql);
    }

    //查询数据字典
    public static List<Map<String, Object>> queryDataDictionary(String tableName, String parentCode) {
        String sql = "select * from " + tableName.toUpperCase() + " order by " + parentCode;
        return jdbcTemplate.queryForList(sql);
    }

    //查询数据字典
    public static List<Map<String, Object>> queryDataDictionary(String tableName, String parentTable, String parentCode, String parentValue, String code, String value, String parentCommonId, String commonId) {
        String sql = "select t1."+parentCode+",t1."+parentValue+",t2."+code+",t2."+value+" from " + parentTable + " t1 inner join " + tableName + " t2 on t1." + commonId + "=t2." + parentCommonId + " order by t1." + parentCode;
        return jdbcTemplate.queryForList(sql);
    }

    //查询表主键字段
    public static Map<String, String> queryTablePk(String tableName) {
        Map<String, String> columns = new HashMap<>();
        String sql = "select col.column_name from user_constraints con,  user_cons_columns col " +
                "where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name ='" + tableName.toUpperCase() + "' ";
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> map : mapList) {
            for (String key : map.keySet()) {
                columns.put(map.get(key).toString(), "*");
            }
        }
        return columns;
    }

    public static void MergeSpaceRow(HSSFSheet sheet) {
        int sk;
        if (sheet.getLastRowNum() > 2) {//将为空的单元格与之前不为空的合并
            sk = 1;
            for (int i = 3; i <= sheet.getLastRowNum(); i++) {
                HSSFRow rows = sheet.getRow(i);
                HSSFCell cell_0 = rows.getCell(1);
                if (cell_0.getStringCellValue() == "") {//如果为空但还没对比到最后一行，继续循环
                    if (i == sheet.getLastRowNum()) {
                        sheet.addMergedRegion(new CellRangeAddress(sk, i, 1, 1));//如果已经对比到最后一行，开始合并
                        sheet.addMergedRegion(new CellRangeAddress(sk, i, 0, 0));//如果已经对比到最后一行，开始合并
                    }
                } else {
                    if (sk != i - 1) {//不为空且i-1不为sk则合并
                        sheet.addMergedRegion(new CellRangeAddress(sk, i - 1, 1, 1));//起始行号，终止行号， 起始列号，终止列号
                        sheet.addMergedRegion(new CellRangeAddress(sk, i - 1, 0, 0));//起始行号，终止行号， 起始列号，终止列号
                    }
                    sk = i;
                }
            }
        }
    }
}
